﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using Zimmerreservierung.Model;

namespace Zimmerreservierung.Controller
{
    public class ReservierungsController
    {
        private const double Stornokosten_in_Prozent = 0.10;

        private static ReservierungsController instance;
        private SQLiteConnection connection;

        public ReservierungsController()
        {
            connection = new SQLiteConnection(Properties.Settings.Default.dbConnectionString);
        }

        public static ReservierungsController getInstance()
        {
            if (instance == null)
                instance = new ReservierungsController();

            return instance;
        }

        public Reservierung GetReservierung(int id, bool storniert) 
        {
            Reservierung reservierung = new Reservierung();

            string sql = "SELECT r.*, k.id as KId, k.name as KName, z.id as ZId, z.name as ZName " +
                                "FROM reservierung r " +
                                "JOIN kunde k ON (r.KundenId = k.id) " +
                                "JOIN zimmer z ON (r.ZimmerId = z.id) " +
                                "WHERE r.id = " + id + " and r.storniert = " + Convert.ToInt32(storniert) +
                                " AND r.RechnungsId IS NULL " +
                                " ORDER BY r.startdatum, r.enddatum";

            try
            {
                connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(connection))
                {
                    cmd.CommandText = sql;

                    SQLiteDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        reservierung = new Reservierung
                        {
                            Id = Convert.ToInt32(reader["id"]),
                            Rabatt = reader["Rabatt"] != DBNull.Value ? Convert.ToInt32(reader["Rabatt"]) * 100 : 0,
                            Zimmerpreis = Convert.ToDouble(reader["Zimmerpreis"]),
                            Zimmerpreisart = Convert.ToString(reader["Zimmerpreisart"]),
                            Storniert = Convert.ToBoolean(reader["Storniert"]),
                            Startdatum = Convert.ToDateTime(reader["Startdatum"]),
                            Enddatum = Convert.ToDateTime(reader["Enddatum"]),
                            FruehzeitigesEnddatum = reader["FruehzeitigesEnddatum"] != DBNull.Value ? Convert.ToDateTime(reader["FruehzeitigesEnddatum"]) : (DateTime?)null,

                            Kunde = new Kunde
                            {
                                Id = Convert.ToInt32(reader["KId"]),
                                Name = Convert.ToString(reader["KName"])
                            },
                            KundeName = Convert.ToString(reader["KName"]),

                            Zimmer = new Zimmer()
                            {
                                Id = Convert.ToInt32(reader["ZId"]),
                                Name = Convert.ToString(reader["ZName"])
                            },
                            ZimmerName = Convert.ToString(reader["ZName"]),
                            Rechnung = new Rechnung()
                            {
                                Id = reader["RechnungsId"] != DBNull.Value ? Convert.ToInt32(reader["RechnungsId"]) : -1
                            }
                        };
                    }
                }

            }
            catch
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

            return reservierung;
        }

        public List<Reservierung> GetReservierungen(bool storniert)  
        {
            var sql = "SELECT r.*, k.id as KId, k.name as KName, z.id as ZId, z.name as ZName " +
                                "FROM reservierung r " +
                                "JOIN kunde k ON (r.KundenId = k.id) " +
                                "JOIN zimmer z ON (r.ZimmerId = z.id) " +
                                "WHERE r.storniert = " + Convert.ToInt32(storniert) +
                                " AND r.RechnungsId IS NULL " +
                                " ORDER BY r.startdatum, r.enddatum";

            List<Reservierung> reservierungen = new List<Reservierung>();
             
            try
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = sql;
                    SQLiteDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Reservierung r = new Reservierung()
                        {
                            Id = Convert.ToInt32(reader["id"]),
                            Rabatt = reader["Rabatt"] != DBNull.Value ? Convert.ToInt32(reader["Rabatt"]) * 100 : 0,
                            Zimmerpreis = Convert.ToDouble(reader["Zimmerpreis"]),
                            Zimmerpreisart = Convert.ToString(reader["Zimmerpreisart"]),
                            Storniert = Convert.ToBoolean(reader["Storniert"]),
                            Startdatum = Convert.ToDateTime(reader["Startdatum"]),
                            Enddatum = Convert.ToDateTime(reader["Enddatum"]),
                            FruehzeitigesEnddatum = reader["FruehzeitigesEnddatum"] != DBNull.Value ? Convert.ToDateTime(reader["FruehzeitigesEnddatum"]) : (DateTime?)null,

                            Kunde = new Kunde 
                            { 
                                Id = Convert.ToInt32(reader["KId"]),
                                Name = Convert.ToString(reader["KName"])
                            },
                            KundeName = Convert.ToString(reader["KName"]),

                            Zimmer = new Zimmer()
                            {
                                Id = Convert.ToInt32(reader["ZId"]),
                                Name = Convert.ToString(reader["ZName"])
                            },
                            ZimmerName = Convert.ToString(reader["ZName"]),
                            Rechnung = new Rechnung()
                            {
                                Id = reader["RechnungsId"] != DBNull.Value ? Convert.ToInt32(reader["RechnungsId"]) : -1
                            }
                        };

                        reservierungen.Add(r);
                    }    
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

            return reservierungen;
        }

        public bool StoreReservierung(int kundeId, int zimmerId, string rabatt, string zimmerpreis, string ZimmerPreisArt, string startDatum, string endDatum)
        {
            int insertOK = 0;

            try
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "INSERT INTO Reservierung (ID, KundenId, ZimmerId, RechnungsId, Rabatt, Zimmerpreis, Zimmerpreisart, Storniert, Startdatum, Enddatum, FruehzeitigesEnddatum ) "
                                                    + "VALUES (NULL, " + kundeId + ", " + zimmerId + ", NULL, '" + rabatt + "', '" + zimmerpreis
                                                    + "', '" + ZimmerPreisArt + "', 0, '" + startDatum + "', '" + endDatum + "', NULL );";
                    insertOK = command.ExecuteNonQuery();
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                connection.Close();
            }
                
            return insertOK == 1 ? true : false;
        }

        public bool Stornieren(int id) 
        {
            int updateOK = 0;

            try
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "UPDATE Reservierung SET Storniert=1 WHERE ID = " + id;
                    updateOK = command.ExecuteNonQuery();
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

            return updateOK == 1 ? true : false;
        }

        public int FruehzeitigeAbreise(int id, DateTime fruehzeitigesEndDatum)
        {
            Reservierung reservierung = GetReservierung(id, false);
            if (DateTime.Compare(reservierung.Startdatum, fruehzeitigesEndDatum) > 0)
                return -1;

            if (DateTime.Compare(reservierung.Enddatum, fruehzeitigesEndDatum) <= 0)
               return -2;

            int updateOK = 0;
            try
            {

                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "UPDATE Reservierung SET FruehzeitigesEnddatum='" + fruehzeitigesEndDatum.ToString("yyyy-MM-dd HH:mm:ss.fff") + "' WHERE ID = " + reservierung.Id + ";";
                    updateOK = command.ExecuteNonQuery();
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

            return updateOK;
        }

        public List<Zimmer> GetFreieZimmer(string von, string bis)
        {
            List<Zimmer> zimmerList = new List<Zimmer>();

            string sql = "Select * from Zimmer where ID not in " +
                         "(Select ZimmerId from Reservierung where Storniert == 0 and " +
                         "(('" + von + "' between Startdatum and Enddatum) or " +
                         "('" + bis + "' between Startdatum and Enddatum))) " +
                         "ORDER BY name";

            try
            {
                connection.Open();
                using (SQLiteCommand cmd = new SQLiteCommand(connection))
                {
                    cmd.CommandText = sql;

                    SQLiteDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        Zimmer z = new Zimmer
                        {
                            Id = Convert.ToInt32(reader["id"]),
                            Name = Convert.ToString(reader["name"]),
                            Belegzahl = Convert.ToInt32(reader["MaxBelegzahl"]),
                            Erwachsener = Convert.ToDouble(reader["PreisEZ"]),
                            ZweiErwachsene = Convert.ToDouble(reader["PreisDZ"]),
                            DreiErwachsene = Convert.ToDouble(reader["PreisDreifachZ"]),
                            ErwachsenerKind = Convert.ToDouble(reader["PreisEZmitKind"]),
                            ErwachsenerZweiKinder = Convert.ToDouble(reader["PreisEZmit2Kind"]),
                            ZweiErwachseneKind = Convert.ToDouble(reader["PreisDZmitKind"]),
                        };

                        zimmerList.Add(z);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                connection.Close();
            }

            return zimmerList;
        }

        public double CalculateKosten(DateTime startdatum, DateTime enddatum, int rabatt, double zimmerKosten, int kundeRabatt) 
        {
            TimeSpan anzahlTage = (enddatum - startdatum).Add(TimeSpan.FromDays(1));

            double rbt = (rabatt / 100.0) + (kundeRabatt / 100.0);
            double preis = zimmerKosten * Convert.ToDouble(anzahlTage.Days);

            double preisRabatt = preis * rbt;

            return preis - preisRabatt;
        }

        public double GetStornierungskosten(int id)
        {
            Reservierung r = GetReservierung(id, false);
            return r.Zimmerpreis * Stornokosten_in_Prozent;
        }

        public void startConnection(String connectionString)
        {
            connection.Close();
            connection = new SQLiteConnection(connectionString);
        }
    }
}
